CREATE DATABASE IF NOT EXISTS sports_club_db;
USE sports_club_db;

CREATE TABLE users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(60) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE athletes (
  athlete_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  age INT NOT NULL,
  sport VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE sessions (
  session_id INT AUTO_INCREMENT PRIMARY KEY,
  session_name VARCHAR(100) NOT NULL,
  session_date DATE NOT NULL,
  coach VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE attendance (
  attendance_id INT AUTO_INCREMENT PRIMARY KEY,
  athlete_id INT NOT NULL,
  session_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  FOREIGN KEY (athlete_id) REFERENCES athletes(athlete_id) ON DELETE CASCADE,
  FOREIGN KEY (session_id) REFERENCES sessions(session_id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO athletes(name, age, sport) VALUES
('David Hill', 20, 'Football'),
('Mark Osei', 22, 'Basketball'),
('Aisha Khan', 19, 'Football'),
('Maria Singh', 21, 'Athletics');

INSERT INTO sessions(session_name, session_date, coach) VALUES
('Morning Training', '2024-01-20', 'Coach Williams'),
('Evening Fitness', '2024-01-25', 'Coach Carter');

INSERT INTO attendance(session_id, athlete_id, status) VALUES
(1, 1, 'Present'),
(1, 2, 'Absent'),
(2, 1, 'Present');

-- Admin password = Admin123!
-- This hash works with password_verify():
INSERT INTO users(username, password_hash) VALUES
('admin', '$2y$10$wHh7fV8m9w3jVYwZg1bF7u2K8o7dQ2qg3p3mV9KZ8cQGQmX3r0g0u');
